# encodong:utf-8
import pymysql
from bs4 import BeautifulSoup
import urllib.request,urllib.parse
import ssl
from lxml import etree
import requests
import re
import urllib3
import time
import datetime
import csv
# from xlutils.copy import copy
import xlrd
import pandas as pd
import numpy
from nose import selector

cookies = 'jsid=https%3A%2F%2Fwww.tianyancha.com%2F%3Fjsid%3DSEM-BAIDU-PZ-SY-2021112-JRGW; ssuid=4279527930; TYCID=d66c9f60bb9b11ebaa0167e7b0b72860; undefined=d66c9f60bb9b11ebaa0167e7b0b72860; _ga=GA1.2.828184792.1621756358; creditGuide=1; bad_id658cce70-d9dc-11e9-96c6-833900356dc6=551f07e1-bbaf-11eb-9bbf-4b0d779ef7ed; _gid=GA1.2.1963969309.1622201393; bdHomeCount=2; CT_TYCID=a31d1063875940d5851f29f09e442f21; RTYCID=7c4fda1742074c93b1269442a033905e; tyc-user-phone=%255B%252218613838138%2522%252C%2522138%25200544%25205509%2522%255D; searchSessionId=1622258389.09386256; aliyungf_tc=df909348cf88cc41eb11e2d012708617b95ecd11a72714e8dc26432d10399b7c; csrfToken=u5YOk2FIVNvuixkUlddcDneq; bannerFlag=true; Hm_lvt_e92c8d65d92d534b0fc290df538b4758=1622249450,1622249500,1622249663,1622286937; tyc-user-info={%22state%22:%225%22%2C%22vipManager%22:%220%22%2C%22isExpired%22:%220%22%2C%22mobile%22:%2218613838138%22}; tyc-user-info-save-time=1622368719118; auth_token=eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiIxODYxMzgzODEzOCIsImlhdCI6MTYyMjM2ODcyMCwiZXhwIjoxNjUzOTA0NzIwfQ.I-fzu1MH7IQBbdm3hZZTKi43wHVog0zyZ9v5V-iK8m2QRifjI_jE9M-8TZq8AkMQIiFFStkuE5NePEXD-ITzTw; relatedHumanSearchGraphId=3361640448; relatedHumanSearchGraphId.sig=BhUitQBLOyxCqJvP1LULLeGcQYLC60RUBQOvRLI9I4w; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2218613838138%22%2C%22first_id%22%3A%221799836236826d-0e9408ac382ea9-3f356b-1327104-179983623691e5%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%221799836236826d-0e9408ac382ea9-3f356b-1327104-179983623691e5%22%7D; acw_tc=781bad1116223708691748515e18a8f1d5469f5aad4e2727bd91649c33b399; acw_sc__v2=60b36a35eef2f28d31fc9b2d3bade2770d021bf1; _gat_gtag_UA_123487620_1=1; Hm_lpvt_e92c8d65d92d534b0fc290df538b4758=1622370874; cloud_token=8f1bd31899814b38986a2489bfb336c6; cloud_utm=90b0382b0a454da688ad2c973df5f41c'
cookie = {i.split("=")[0]:i.split("=")[1] for i in cookies.split(";")}
headers = {
                'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36',
                # 'Cookie':'jsid=https%3A%2F%2Fwww.tianyancha.com%2F%3Fjsid%3DSEM-BAIDU-PZ-SY-2021112-JRGW; ssuid=4279527930; TYCID=d66c9f60bb9b11ebaa0167e7b0b72860; undefined=d66c9f60bb9b11ebaa0167e7b0b72860; _ga=GA1.2.828184792.1621756358; creditGuide=1; bad_id658cce70-d9dc-11e9-96c6-833900356dc6=551f07e1-bbaf-11eb-9bbf-4b0d779ef7ed; _gid=GA1.2.1963969309.1622201393; bdHomeCount=2; CT_TYCID=a31d1063875940d5851f29f09e442f21; RTYCID=7c4fda1742074c93b1269442a033905e; tyc-user-phone=%255B%252218613838138%2522%252C%2522138%25200544%25205509%2522%255D; searchSessionId=1622258389.09386256; aliyungf_tc=df909348cf88cc41eb11e2d012708617b95ecd11a72714e8dc26432d10399b7c; csrfToken=u5YOk2FIVNvuixkUlddcDneq; bannerFlag=true; Hm_lvt_e92c8d65d92d534b0fc290df538b4758=1622249450,1622249500,1622249663,1622286937; acw_tc=781bad4416223686993277637e109456cd92024a6e30df99332aa91ee7ceeb; acw_sc__v2=60b361bb14331e8f90c0199be1bba3f019e54559; tyc-user-info={%22state%22:%225%22%2C%22vipManager%22:%220%22%2C%22isExpired%22:%220%22%2C%22mobile%22:%2218613838138%22}; tyc-user-info-save-time=1622368719118; auth_token=eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiIxODYxMzgzODEzOCIsImlhdCI6MTYyMjM2ODcyMCwiZXhwIjoxNjUzOTA0NzIwfQ.I-fzu1MH7IQBbdm3hZZTKi43wHVog0zyZ9v5V-iK8m2QRifjI_jE9M-8TZq8AkMQIiFFStkuE5NePEXD-ITzTw; relatedHumanSearchGraphId=3361640448; relatedHumanSearchGraphId.sig=BhUitQBLOyxCqJvP1LULLeGcQYLC60RUBQOvRLI9I4w; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2218613838138%22%2C%22first_id%22%3A%221799836236826d-0e9408ac382ea9-3f356b-1327104-179983623691e5%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%221799836236826d-0e9408ac382ea9-3f356b-1327104-179983623691e5%22%7D; Hm_lpvt_e92c8d65d92d534b0fc290df538b4758=1622369858'

}
def get_url_tree(url):
    # 对网站发起请求
    page_test = requests.get(url=url, headers=headers,cookies=cookie).text
    # print(page_test)
    # 这里是将从互联网上获取的源码数据加载到该对象中
    tree = etree.HTML(page_test)
    return tree

with open('Data/企业.txt', encoding='utf-8') as file:
    line_list = [k.strip() for k in file.readlines()]  # 用 strip()移除末尾的空格
shifou = []
i = 1
for word in line_list:
    #先打开一个网址
    url = 'https://www.tianyancha.com/search?key='+urllib.parse.quote(word)
    print(url)
    # 在获取第二个url
    tree = get_url_tree(url)
    print(tree)
    # // *[ @ id = "search_company_0"] / div / div[3] / div[1] / a
    # result =  re.findall('<div.*?><a.*?href="(.*?)">.*?</a>.*?<div>', re.S)
    url_list = tree.xpath('// *[ @ id = "search_company_0"] / div / div[3] / div[1] / a/ @href')
    print(url_list)
    page_test = requests.get(url=url_list[0], headers=headers,cookies=cookie).text
    print(page_test)
    time.sleep(30)
    soup = BeautifulSoup(page_test, 'html.parser')  # 创建soup对象，获取html代码
    tree = get_url_tree(url_list[0])
    pingfen = soup.find('span', class_="sort-score-value").get_text()
    renyuanguimo = tree.xpath('// *[ @ id = "_container_baseInfo"] / table / tbody / tr[7] / td[6]/ text()')[0]
    yingyeqixian = tree.xpath('// *[ @ id = "_container_baseInfo"] / table / tbody / tr[6] / td[2] / span/ text()')[0]
    # zhucedizhi = tree.xpath('// *[ @ id = "_container_baseInfo"] / table / tbody / tr[10] / td[2] / text()')[0]

    id = url_list[0].split('//')[1]
    cid = id.split('/', 2)[2]
    print(cid)
    # 从而获得最终的url

    urls = 'https://www.tianyancha.com/risk/detail?watchType=1&cid=' + cid + '&source=company'
    print(urls)
    page_test = requests.get(url=urls, headers=headers, cookies=cookie).text
    soup = BeautifulSoup(page_test, 'html.parser')  # 创建soup对象，获取html代码
    fengxianshu = soup.find('span', class_="risk-count").get_text()
    shixinfou = soup.find('span', class_="normal-color").get_text()
    shijian = time.strftime('%Y-%m-%d', time.localtime())
    print(time)  # 时间格式化
    print("风险总数" + ":" + fengxianshu)
    print("是否进入失信名单" + ":" + shixinfou)
    print("评分:" + pingfen)
    print("人员规模:" + renyuanguimo)
    print('营业期限:' + yingyeqixian)
    conn = pymysql.connect(host="127.0.0.1", user="root", password="243420", database="enterprise",
                           charset="utf8")
    conn1 = pymysql.connect(host="127.0.0.1", user="root", password="243420", database="enterprise",
                           charset="utf8")
    cursor = conn.cursor()
    value = (word, shijian, shixinfou)
    sql = "INSERT INTO company(company_name,last_craw_time,dishonor_flag)VALUES(%s,%s,%s)"
    cursor.execute(sql, value)  # 执行sql语句
    conn.commit()
    cursor1 = conn1.cursor()
    value1 = (cid, shijian, pingfen, renyuanguimo, yingyeqixian, shixinfou, fengxianshu, urls)
    sql1 = "INSERT INTO crawling_details(crawling_time,score,personnel_size,business_term,dishonor_flag,total_risk,crawling_url)VALUES(%s,%s,%s,%s,%s,%s,%s)"
    cursor1.execute(sql1, value1)  # 执行sql语句
    conn1.commit()
    print("插入成功")
    cursor.close()  # 关闭连接
    i = i + 1
    time.sleep(30)
print("结束·")

